package DBLayer;
import ModelLayer.PostalCity;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Henrik-iMac
 */


public class DBPostalCity implements IFDBPostalCity {
    private Connection con;

    /** Creates a new instance of DBPostalCity*/
    public DBPostalCity(){
        con = DbConnection.getInstance().getDBcon();
    }

    //get all cities
    public ArrayList<PostalCity> getAllCities(){
        ArrayList postalList = new ArrayList();
        postalList = miscWhere("");
        return postalList;
    }
    //get one city have the zipcode
    public PostalCity findCityZipCode(int zipCode){
        String wClause = "postalCode = " + zipCode;
        System.out.println("Search city: " + wClause);
        return singleWhere(wClause);
    }
    public PostalCity findCityByName(String city){
        String wClause = "city like '%" + city + "%'";
        System.out.println("Search city: " + wClause);
        return singleWhere(wClause);
    }

   //insert a new city
    public int insertCity(PostalCity city){
        int rc = -1;
        String query ="INSERT INTO PostalCity(postalCode, city) VALUES("+
                city.getPostalCode() + ",'"+
                city.getCity() +"')";
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in PostalCity db: "+e);
        }
        return(rc);
    }
    //update information about a city
    public int updateCity(PostalCity city){
        PostalCity postObj = city;
        int rc = -1;

        String query = "UPDATE PostalCity SET " +
                "postalCode ="+ postObj.getPostalCode()+","+
                "city ='"+ postObj.getCity()+"' "+
                " WHERE postalCode = " + postObj.getPostalCode();
        System.out.println("Update query: "+query);
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }
        catch (Exception e)
        {
            System.out.println("Update exception in PostalCity db: "+e);
        }
        return (rc);
    }

    private ArrayList<PostalCity> miscWhere(String wClause){
        ResultSet results;
        ArrayList<PostalCity> list = new ArrayList<PostalCity>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;

            while(results.next()){
                PostalCity postObj = new PostalCity();
                postObj = buildPostalCity(results);
                list.add(postObj);
            }
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    private PostalCity singleWhere(String wClause){
        ResultSet results;
        PostalCity postObj = new PostalCity();
        String query = buildQuery(wClause);
        System.out.println(query);

        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                postObj = buildPostalCity(results);

                stmt.close();
            }

        }
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return postObj;
    }

    private String buildQuery(String wClause){
        String query="SELECT * FROM PostalCity";

        if(wClause.length() > 0)
        {
            query=query+" WHERE " + wClause;
        }
        return query;
    }

    private PostalCity buildPostalCity(ResultSet results)
    {
        PostalCity postObj = new PostalCity();
        try{
            postObj.setPostalCode(results.getInt(1));
            postObj.setCity(results.getString(2));
        }
        catch (Exception e){
            System.out.println("error in building the employee object");
        }
        return postObj;
    }
}
